<%@ page import="java.sql.*"%>
<%@ page import="java.util.*"%>
<%@ page import="ca.radiologydb.util.*"%>

<!-- @author Nicholas Liu -->
<!-- Prints the report that was requested on report.jsp -->

<%
	Template template = new Template(this.getServletContext()
			.getRealPath("/").toString());
	out.println(template.getTemplateTop());
	out.println("<h3>Report Results for Diagnosis: "+request.getParameter("diag")+"</h3>");
	// user access module.
	if (session.getAttribute("username") == null
			|| session.getAttribute("usertype") == null) {
		out.println("Cannot display results as you're not logged in, or do not have the proper permissions.");
		out.println("<a href='login.jsp'>Login</a>");
	} else if (!session.getAttribute("usertype").equals("a")) {
		out.println("Cannot display results as you're not logged in, or do not have the proper permissions.");
		out.println("<a href='login.jsp'>Login</a>");

	} else {
		ConnectionManager manager = new ConnectionManager();
		Connection conn = manager.getConnection();

		Statement stmt = null;
		ResultSet rset = null;
		boolean add = false;

		String sql = "";

		// initial sql query to get min test dates for all patients with specified diag.
		sql += "SELECT min(test_date), patient_name ";
		sql += "FROM radiology_record ";
		sql += "WHERE diagnosis= '" + request.getParameter("diag")
				+ "' ";
		
		String startdate = request.getParameter("start_date");
		String enddate = request.getParameter("end_date");
		
		// add the appropriate date gating.
		if (startdate != null && !startdate.trim().equals("")) {
			sql += " AND test_date > to_date('" + startdate
					+ "','mm-dd-yyyy') ";
		}

		if (enddate != null && !enddate.trim().equals("")) {
			sql += " AND test_date < to_date('" + enddate
					+ "','mm-dd-yyyy') ";
		}

		sql += " GROUP BY patient_name";

		// execute query.
		stmt = conn.createStatement();
		rset = stmt.executeQuery(sql);
		
		// print result table.
		out.println("<table border=1>");
		out.println("<tr>");
		out.println("<th>Test Date</th>");
		out.println("<th>Patient Username</th>");
		out.println("<th>Patient Name</th>");
		out.println("<th>Address</th>");
		out.println("<th>Phone</th>");
		out.println("</tr>");
		while (rset != null && rset.next()) {
			// secondary query to access persons table, per user_name in first query.
			Statement stmt2 = conn.createStatement();
			ResultSet rset2;
			String sql2 = "";
			String username = rset.getString(2);
			sql2 += "SELECT first_name || ' ' || last_name, address, phone ";
			sql2 += "FROM persons ";
			sql2 += "WHERE user_name = '"+username+"'";
			rset2 = stmt2.executeQuery(sql2);
			rset2.next();
			out.println("<tr>");
			out.println("<td>");
			out.println(rset.getDate(1));
			out.println("</td>");
			out.println("<td>");
			out.println(username);
			out.println("</td>");
			out.println("<td>");
		 	out.println(rset2.getString(1));
			out.println("</td>");
			out.println("<td>");
			out.println(rset2.getString(2));
			out.println("</td>");
			out.println("<td>");
			out.println(rset2.getString(3));
			out.println("</td>");
			out.println("</tr>");
			
			stmt2.close();
		}
		
		
		stmt.close();
		
		conn.close();
		
		manager.closeConnection();

	}
	out.println(template.getTemplateBottom());
%>